package com.zm.blog.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zm.blog.entity.HistoryInfo;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 历史信息 Mapper 接口
 */
public interface HistoryInfoMapper extends BaseMapper<HistoryInfo> {

    /**
     * 访问IP最多的15个省份或其他地区
     */
    @Select("SELECT nation, province, COUNT(DISTINCT ip) AS num FROM history_info WHERE nation IS NOT NULL AND " +
            "province IS NOT NULL GROUP BY nation, province ORDER BY num DESC LIMIT 15")
    List<Map<String, Object>> getHistoryByProvince();

    /**
     * 访问次数最多的15个IP
     */
    @Select("SELECT ip, COUNT(*) AS num FROM history_info GROUP BY ip ORDER BY num DESC LIMIT 15")
    List<Map<String, Object>> getHistoryByIp();

    /**
     * 访问48小时内的数据
     */
    @Select("SELECT ip, user_id, nation, province FROM history_info WHERE create_time >= (NOW() - INTERVAL 48 HOUR)")
    List<Map<String, Object>> getHistoryBy24Hour();

    /**
     * 总访问量
     */
    @Select("SELECT COUNT(*) FROM history_info")
    Long getHistoryCount();

}
